﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Text;

namespace XianShang.Data.SqlServer
{
    public partial class DataProvider : IDataProvider
    {
        #region 匯款
        
        /// <summary>
        /// 取得最近30天匯款成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneMonth_Remit(string area)
        {
            string commandText = "SELECT SUM(Amount) AS [Amount],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_remitbank] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-31))+'-'+Convert(varchar,DatePart(month,getdate()-31))+'-'+Convert(varchar,DatePart(day,getdate()-31))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text,commandText);
        }
        public DataSet GetNearOneMonthNum_Remit(string area)
        {
            string commandText = "SELECT SUM(AccountNum) AS [AccountNum],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_remitbank] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-31))+'-'+Convert(varchar,DatePart(month,getdate()-31))+'-'+Convert(varchar,DatePart(day,getdate()-31))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        ///  取得最近7天匯款成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneWeek_Remit(string area)
        {
            string commandText = "SELECT SUM(Amount) AS [Amount],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_remitbank] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-6))+'-'+Convert(varchar,DatePart(month,getdate()-6))+'-'+Convert(varchar,DatePart(day,getdate()-6))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }

        /// <summary>
        ///  取得最近7天匯款成交數量
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneWeekNum_Remit(string area)
        {
            string commandText = "SELECT SUM(AccountNum) AS [AccountNum],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_remitbank] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-6))+'-'+Convert(varchar,DatePart(month,getdate()-6))+'-'+Convert(varchar,DatePart(day,getdate()-6))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }

        /// <summary>
        ///  取得某一天成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneDayNum_Remit(string area, DateTime dt)
        {
            string commandText = string.Format("SELECT SUM(AccountNum) AS [AccountNum],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_remitbank] ", dt.Date);
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format(" AND  DateDiff(DAY,[time],'{0}')=0", dt.Date.ToString("yyyy-MM-dd"));
            commandText += " GROUP BY CONVERT(char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        ///  取得某一天成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneDay_Remit(string area, DateTime dt)
        {
            string commandText = string.Format("SELECT SUM(Amount) AS [Amount],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_remitbank] ", dt.Date);
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format(" AND  DateDiff(DAY,[time],'{0}')=0", dt.Date.ToString("yyyy-MM-dd"));
            commandText += " GROUP BY CONVERT(char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }

        /// <summary>
        /// 取得某個月匯款成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetYearMonth_Remit(string area,string year,string month)
        {
            string commandText = "SELECT SUM(Amount) AS [Amount],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_remitbank] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format("AND YEAR([dbo].[XS_RemitBank].[time])={0} AND MONTH([dbo].[XS_RemitBank].[time])={1}",year,month);
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        /// 某個月匯款成交數量
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetYearMonthNum_Remit(string area, string year, string month)
        {
            string commandText = "SELECT SUM(AccountNum) AS [AccountNum],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_remitbank] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format("AND YEAR([dbo].[XS_RemitBank].[time])={0} AND MONTH([dbo].[XS_RemitBank].[time])={1}", year, month);
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        #endregion

        #region swapcard
        /// <summary>
        ///  取得某一天成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetOneDayNum_SwapCard(string area, DateTime dt)
        {
            string commandText = string.Format("SELECT SUM(CardNum) AS [CardNum],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_CardSwap] ", dt.Date);
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format(" AND  DateDiff(DAY,[time],'{0}')=0", dt.Date.ToString("yyyy-MM-dd"));
            commandText += " GROUP BY CONVERT(char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        /// 進一個月交易數量
        /// </summary>
        /// <param name="area"></param>
        /// <returns></returns>
        public DataSet GetNearOneMonthNum_SwapCard(string area)
        {
            string commandText = "SELECT SUM(CardNum) AS [CardNum],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_CardSwap] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-31))+'-'+Convert(varchar,DatePart(month,getdate()-31))+'-'+Convert(varchar,DatePart(day,getdate()-31))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        /// 某個月匯款成交數量
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetYearMonthNum_SwapCard(string area, string year, string month)
        {
            string commandText = "SELECT SUM(CardNum) AS [CardNum],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_CardSwap] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format("AND YEAR([time])={0} AND MONTH([time])={1}", year, month);
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        ///  取得最近7天匯款成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneWeekNum_SwapCard(string area)
        {
            string commandText = "SELECT SUM(CardNum) AS [CardNum],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_CardSwap] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-6))+'-'+Convert(varchar,DatePart(month,getdate()-6))+'-'+Convert(varchar,DatePart(day,getdate()-6))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        #endregion

        #region moveaccount
        /// <summary>
        ///  取得某一天成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetOneDayNum_MoveAccount(string area, DateTime dt)
        {
            string commandText = string.Format("SELECT Count(id) AS [Num],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_MoveAccount] ", dt.Date);
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format(" AND  DateDiff(DAY,[time],'{0}')=0", dt.Date.ToString("yyyy-MM-dd"));
            commandText += " GROUP BY CONVERT(char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        /// 進一個月交易數量
        /// </summary>
        /// <param name="area"></param>
        /// <returns></returns>
        public DataSet GetNearOneMonthNum_MoveAccount(string area)
        {
            string commandText = "SELECT Count(id) AS [Num],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_MoveAccount] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-30))+'-'+Convert(varchar,DatePart(month,getdate()-30))+'-'+Convert(varchar,DatePart(day,getdate()-30))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        /// 某個月匯款成交數量
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetYearMonthNum_MoveAccount(string area, string year, string month)
        {
            string commandText = "SELECT Count(id) AS [Num],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_MoveAccount] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format("AND YEAR([time])={0} AND MONTH([time])={1}", year, month);
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        ///  取得最近7天匯款成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneWeekNum_MoveAccount(string area)
        {
            string commandText = "SELECT Count(id) AS [Num],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_MoveAccount] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-6))+'-'+Convert(varchar,DatePart(month,getdate()-6))+'-'+Convert(varchar,DatePart(day,getdate()-6))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        #endregion

        #region IBON匯款

        /// <summary>
        /// 取得最近30天匯款成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneMonth_Ibon(string area)
        {
            string commandText = "SELECT SUM(Amount) AS [Amount],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_ibonpay] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-31))+'-'+Convert(varchar,DatePart(month,getdate()-31))+'-'+Convert(varchar,DatePart(day,getdate()-31))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        public DataSet GetNearOneMonthNum_Ibon(string area)
        {
            string commandText = "SELECT COUNT(ID) AS [Num],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_ibonpay] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-31))+'-'+Convert(varchar,DatePart(month,getdate()-31))+'-'+Convert(varchar,DatePart(day,getdate()-31))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        ///  取得最近7天匯款成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneWeek_Ibon(string area)
        {
            string commandText = "SELECT SUM(Amount) AS [Amount],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_ibonpay] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-6))+'-'+Convert(varchar,DatePart(month,getdate()-6))+'-'+Convert(varchar,DatePart(day,getdate()-6))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }

        /// <summary>
        ///  取得最近7天匯款成交數量
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneWeekNum_Ibon(string area)
        {
            string commandText = "SELECT COUNT(ID) AS [Num],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_ibonpay] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += "AND [time]>=Convert(varchar,DatePart(year,getdate()-6))+'-'+Convert(varchar,DatePart(month,getdate()-6))+'-'+Convert(varchar,DatePart(day,getdate()-6))";
            commandText += "AND [time]<=getdate() ";
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }

        /// <summary>
        ///  取得某一天成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneDayNum_Ibon(string area, DateTime dt)
        {
            string commandText = string.Format("SELECT COUNT(ID) AS [Num],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_ibonpay] ", dt.Date);
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format(" AND  DateDiff(DAY,[time],'{0}')=0", dt.Date.ToString("yyyy-MM-dd"));
            commandText += " GROUP BY CONVERT(char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        ///  取得某一天成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetNearOneDay_Ibon(string area, DateTime dt)
        {
            string commandText = string.Format("SELECT SUM(Amount) AS [Amount],CONVERT(char(10), Time, 120) As [Time]  FROM [dbo].[xs_ibonpay] ", dt.Date);
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format(" AND  DateDiff(DAY,[time],'{0}')=0", dt.Date.ToString("yyyy-MM-dd"));
            commandText += " GROUP BY CONVERT(char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }

        /// <summary>
        /// 取得某個月匯款成交記錄
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetYearMonth_Ibon(string area, string year, string month)
        {
            string commandText = "SELECT SUM(Amount) AS [Amount],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_ibonpay] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format("AND YEAR([time])={0} AND MONTH([time])={1}", year, month);
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        /// <summary>
        /// 某個月匯款成交數量
        /// </summary>
        /// <param name="area">地區如果為0則代表全部地區</param>
        /// <returns></returns>
        public DataSet GetYearMonthNum_Ibon(string area, string year, string month)
        {
            string commandText = "SELECT COUNT(ID) AS [Num],CONVERT(char(10), Time, 120) As [Time] FROM [dbo].[xs_ibonpay] ";
            commandText += string.Format("WHERE [status]=1 {0}", area != "0" ? string.Format("AND [area]={0}", area) : "");
            commandText += string.Format("AND YEAR([time])={0} AND MONTH([time])={1}", year, month);
            commandText += " GROUP BY CONVERT (char(10), Time, 120)";
            return DbHelper.ExecuteDataset(CommandType.Text, commandText);
        }
        #endregion

    }
}
